package com.navinfo.platform.aero.data.rest.core.service.impl;

import com.navinfo.platform.aero.data.rest.core.entity.ColumnsMapper;
import com.navinfo.platform.aero.data.rest.core.enumref.ValueType;
import com.navinfo.platform.aero.data.rest.core.service.BaseService;
import com.navinfo.platform.aero.data.rest.core.util.StringUtils;
import org.springframework.stereotype.Service;

import java.util.*;

import static java.util.stream.Collectors.groupingBy;


/**
 * 数据分析平台service
 * @time 2018-12-04
 * @author CaiHS
 */
@Service
public class QureyDataService extends BaseService {
    /**
     * 查询映射字段
     * @param parent 父分类
     * @param childs 子分类
     * @param cycle 周期
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryColumns(String parent, List<String> childs, String cycle) throws Exception{
        List<ColumnsMapper> columns = queryRepo.queryColumnsList(null, parent, childs, cycle);
        List<Map<String, Object>> result = new ArrayList<>();
        Map<String, List<ColumnsMapper>> childNode = columns.stream().collect(groupingBy(ColumnsMapper::getChild));
        childNode.forEach((k,v) ->{
            Map<String, Object> jsonNode = new HashMap<>();
            jsonNode.put("child", k);
            List<Map<String, String>> cs = new ArrayList<>();
            Map<String, String> cmap;
            for(ColumnsMapper c : v){
                cmap = new HashMap<>();
                cmap.put("column",c.getKey().trim());
                cmap.put("name",c.getName().trim());
                cs.add(cmap);
            }
            jsonNode.put("columns", cs);
            result.add(jsonNode);
        });
        return result;
    }

    /**
     * 取得对应colume数据
     * @param uid 唯一ID
     * @param start 开始日期
     * @param end 结束日期
     * @param columns columns
     * @param cycle 周期
     * @param parent 父分类
     * @param childs 子分类
     * @return 结果
     * @throws Exception
     */
    public List<Map<String, Object>> queryList(Long uid, String start, String end, List<String> columns, String cycle, String parent, List<String> childs) throws Exception{
        int startInt = Integer.valueOf(start.replace("-",""));
        int endInt = Integer.valueOf(end.replace("-",""));
        List<ColumnsMapper> columnsOut = queryRepo.queryColumnsList(columns, parent, childs, cycle);
        if(columnsOut.isEmpty()) return null;
        String sql = concatSql(columnsOut, cycle);
        List<Map<String, Object>> resultList = queryRepo.queryList(sql, uid , startInt, endInt );
        return resultList;
    }
    public List<Map<String, Object>> queryList(Long uid, String start, String end, String cycle, List<String> parents) throws Exception{
        int startInt = Integer.valueOf(start.replace("-",""));
        int endInt = Integer.valueOf(end.replace("-",""));
        List<ColumnsMapper> columnsOut = queryRepo.queryColumnsList(parents, cycle);
        if(columnsOut.isEmpty()) return null;
        String sql = concatSql(columnsOut, cycle);
        List<Map<String, Object>> resultList = queryRepo.queryList(sql, uid , startInt, endInt );
        return resultList;
    }
    /**
     * 拼接sql
     * @param columns 项目名
     * @param cycle 周期
     * @return sql
     */
    private String concatSql(List<ColumnsMapper> columns, String cycle){
        Map<String, List<ColumnsMapper>> tableArray = columns.stream().collect(groupingBy(col -> {
            String tableName = col.getTable();
            if("1".equals(col.getSuffix())){
                tableName += "_" + cycle;
            }
            return tableName;
        }));
        StringBuilder sqlBf = new StringBuilder();
        StringBuilder selectColumnsBf = new StringBuilder();
        //String whereConditionStr = "";
        StringBuilder joinBf = new StringBuilder();

        sqlBf.append("SELECT ");
        int index = 1;
        String tableNickName;
        Iterator entries = tableArray.entrySet().iterator();
        // main table
        String mainTableNickName = "main.";
        joinBf.append(" FROM");
        joinBf.append(" (");
        joinBf.append(" SELECT :uid uid, CAST(v.day AS UNSIGNED) day FROM");
        joinBf.append("  (SELECT DATE_FORMAT(ADDDATE(:start, INTERVAL (t1.i * 10 + t0.i) " + cycle + "), \"%Y%m%d\") day ");
        joinBf.append("   FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,");
        joinBf.append("      (SELECT 0 i UNION SELECT 1 union SELECT 2 UNION SELECT 3) t1");
        joinBf.append("   ");
        joinBf.append("  ) v WHERE v.day BETWEEN :start AND :end");
        joinBf.append(" ) main ");
        String order = " ORDER BY main.day";
        while (entries.hasNext()) {
            Map.Entry entry = (Map.Entry) entries.next();
            // 表名
//            String tableName = entry.getKey() + "_" + cycle;
            String tableName = (String) entry.getKey();
            // 表别名
            tableNickName = "t" + String.valueOf(index);
            List<ColumnsMapper> value = (List<ColumnsMapper>)entry.getValue();


            String join = " LEFT JOIN " + tableName + " " + tableNickName
                    + " ON " + mainTableNickName + "uid = " + tableNickName + ".uid"
                    + " AND " + mainTableNickName + "day = " + tableNickName + ".day";
            joinBf.append(join);


            // columns
            for(ColumnsMapper c: value){
                String column = c.getColumn();
                String typeCode = c.getType();
                String defaultValue = getTypeDefaultValue(typeCode);
                if(column.contains(",")){
                    String[] split = column.split(",");
                    selectColumnsBf.append(" CONCAT_WS(','");
                    for(String s :split){
                        selectColumnsBf.append(",IFNULL(" + tableNickName + "." + s +"," + defaultValue + ")");
                    }
                    selectColumnsBf.append(") " + c.getKey() + ",");
                } else {
                    selectColumnsBf.append(" IFNULL(" + tableNickName + "." + c.getColumn() + "," + defaultValue + ")" + c.getKey() + ",");
                }
            }
            index++;
        }
        selectColumnsBf.append(mainTableNickName + "uid uid,");
        selectColumnsBf.append("CAST(" + mainTableNickName + "day AS CHAR) day");
        sqlBf.append(selectColumnsBf);
        sqlBf.append(joinBf);
        sqlBf.append(order);
        //sqlBf.append(whereConditionStr);
        return sqlBf.toString();
    }

    /**
     *
     * @param typeCode 该字段返回时的类型(XY) X:0(非Array)，1(Array)；Y：0(Integer),1(String),2(Double)
     * @return
     */
    private String getTypeDefaultValue(String typeCode){
        if(StringUtils.isNull(typeCode) || typeCode.length() < 2) return "0";
        String type = typeCode.substring(1);
        String value = "";
        switch(ValueType.getGather(type)){
            case STRING : break;
            default : value = "0";
        }
        return value;
    }
}
